



ALTER    function fn_get_lot_shipping_flow_by_id (@process_step_id bigint)
returns  @fn_result Table(ProcessStep_ID bigint, 
			PS_Type varchar(100), 
			Process_Type_Name varchar(20))

As
begin
	declare @temp_process_step_id bigint
	declare @temp_ps_type varchar(20)
	declare @temp_ps_type_name varchar(20)
	declare @ps_type varchar(20)
	declare @ps_type_name varchar(20)
	declare @track_id int
	declare @assembly_process_step_id bigint
	declare @assembly_track_id int
	declare @mark_process_step_id bigint
	declare @fg_process_step_id bigint
	declare @sort_track_cursor CURSOR
	declare @mark_fg_track_cursor CURSOR 
	declare @flag int
	declare @temp_assembly_track_id int
	declare @temp_product_name varchar(100)
	declare @result Table(ProcessStep_ID bigint, 
			PS_Type varchar(100), 
			Process_Type_Name varchar(20))	
	
	select @temp_ps_type=PS_Type, @temp_ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @process_step_id


	if @temp_ps_type_name = 'FAB'
	begin
		insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@process_step_id, @temp_ps_type, @temp_ps_type_name)

		select @track_id = Track_ID from Fab_Track_Table where ProcessStep_ID = @process_step_id

		set @sort_track_cursor = CURSOR FOR
		select ProcessStep_ID from Sort_Track_Table where Track_ID = @track_id
		open @sort_track_cursor
			fetch next from @sort_track_cursor into @temp_process_step_id
			while @@fetch_status = 0
			begin
				select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
				insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @ps_type, @ps_type_name)
				fetch next from @sort_track_cursor into @temp_process_step_id
			end
		close @sort_track_cursor
		deallocate @sort_track_cursor


		set @sort_track_cursor = CURSOR for
		select AssemblyTrack_ID, ProcessStep_ID from Assembly_Track_Table where Track_ID = @track_id

		open @sort_track_cursor
			
			fetch next from @sort_track_cursor into @assembly_track_id, @assembly_process_step_id
			while @@fetch_status = 0
			begin
				if @assembly_process_step_id is not null
				begin

					select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @assembly_process_step_id
					select @temp_product_name = Part_Number from Product where ProcessStep_ID =@assembly_process_step_id
					insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@assembly_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
				end
				declare test_track_cursor CURSOR for
				select ProcessStep_ID from Test_Track_Table where AssemblyTrack_ID = @assembly_track_id order by Test_Track_Table.Sequence
				open test_track_cursor
					fetch next from test_track_cursor into @temp_process_step_id
					while @@fetch_status = 0
					begin
						-- print 'AssemblyTrack_ID = '+convert(varchar(20), @assembly_track_id)
						-- print 'ProcessStep_ID = '+convert(varchar(20), @temp_process_step_id)
						if @temp_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@temp_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
						fetch next from test_track_cursor into @temp_process_step_id
					end
				close test_track_cursor
				deallocate test_track_cursor

				set @mark_fg_track_cursor = CURSOR FOR
				select Mark_Pack_ProcessStep_ID, FG_ProcessStep_ID from Mark_FG_Track_Table where AssemblyTrack_ID = @assembly_track_id

				open @mark_fg_track_cursor

					fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
					while @@fetch_status = 0
					begin
						if @mark_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @mark_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@mark_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@mark_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
						if @fg_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @fg_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@fg_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@fg_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
	
						fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
					end

				close @mark_fg_track_cursor
				deallocate @mark_fg_track_cursor

				fetch next from @sort_track_cursor into @assembly_track_id, @assembly_process_step_id
			end
		close @sort_track_cursor
		deallocate @sort_track_cursor
	end
	else if @temp_ps_type_name = 'SORT' or @temp_ps_type_name = 'Wafer Store' or @temp_ps_type_name = 'Die Bank'
	begin
		select @track_id = Track_ID from Sort_Track_Table where ProcessStep_ID = @process_step_id
		set @flag = 0
		set @sort_track_cursor = CURSOR FOR
		select ProcessStep_ID from Sort_Track_Table where Track_ID = @track_id
		open @sort_track_cursor
			fetch next from @sort_track_cursor into @temp_process_step_id
			while @@fetch_status = 0
			begin
				if @temp_process_step_id = @process_step_id
				begin
					set @flag = 1 
				end
				if @flag = 1
				begin
					select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
					insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @ps_type, @ps_type_name)
				end
				fetch next from @sort_track_cursor into @temp_process_step_id
			end
		close @sort_track_cursor
		deallocate @sort_track_cursor

		--declare @assembly_track_id int
		set @sort_track_cursor = CURSOR for
		select AssemblyTrack_ID, ProcessStep_ID from Assembly_Track_Table where Track_ID = @track_id

		open @sort_track_cursor
			-- declare @assembly_process_step_id bigint
			fetch next from @sort_track_cursor into @assembly_track_id, @assembly_process_step_id
			while @@fetch_status = 0
			begin

				if @assembly_process_step_id is not null
				begin
					select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @assembly_process_step_id
					select @temp_product_name = Part_Number from Product where ProcessStep_ID =@assembly_process_step_id
					insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@assembly_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
				end

				declare test_track_cursor CURSOR for
				select ProcessStep_ID from Test_Track_Table where AssemblyTrack_ID = @assembly_track_id order by Test_Track_Table.Sequence
				open test_track_cursor
					fetch next from test_track_cursor into @temp_process_step_id
					while @@fetch_status = 0
					begin
						if @temp_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@temp_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
						fetch next from test_track_cursor into @temp_process_step_id
					end
				close test_track_cursor
				deallocate test_track_cursor
				set @mark_fg_track_cursor = CURSOR FOR
				select Mark_Pack_ProcessStep_ID, FG_ProcessStep_ID from Mark_FG_Track_Table where AssemblyTrack_ID = @assembly_track_id

				open @mark_fg_track_cursor

					fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
					while @@fetch_status = 0
					begin
						if @mark_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @mark_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@mark_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@mark_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
						if @fg_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @fg_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@fg_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@fg_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
	
						fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
					end

				close @mark_fg_track_cursor
				deallocate @mark_fg_track_cursor

				fetch next from @sort_track_cursor into @assembly_track_id, @assembly_process_step_id
			end
		close @sort_track_cursor
		deallocate @sort_track_cursor

	end
	else if @temp_ps_type_name = 'Assembly'
	begin
		set @sort_track_cursor = CURSOR for
		select AssemblyTrack_ID, ProcessStep_ID from Assembly_Track_Table where ProcessStep_ID = @process_step_id

		open @sort_track_cursor
			-- declare @assembly_process_step_id bigint
			fetch next from @sort_track_cursor into @assembly_track_id, @assembly_process_step_id
			while @@fetch_status = 0
			begin

				if @assembly_process_step_id is not null
				begin
					select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @assembly_process_step_id
					insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@assembly_process_step_id, @ps_type, @ps_type_name)
				end

				declare test_track_cursor CURSOR for
				select ProcessStep_ID from Test_Track_Table where AssemblyTrack_ID = @assembly_track_id order by Test_Track_Table.Sequence
				open test_track_cursor
					fetch next from test_track_cursor into @temp_process_step_id
					while @@fetch_status = 0
					begin
						if @temp_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @ps_type, @ps_type_name)
						end
						fetch next from test_track_cursor into @temp_process_step_id
					end
				close test_track_cursor
				deallocate test_track_cursor
				set @mark_fg_track_cursor = CURSOR FOR
				select Mark_Pack_ProcessStep_ID, FG_ProcessStep_ID from Mark_FG_Track_Table where AssemblyTrack_ID = @assembly_track_id

				open @mark_fg_track_cursor

					fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
					while @@fetch_status = 0
					begin
						if @mark_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @mark_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@mark_process_step_id, @ps_type, @ps_type_name)
						end
						if @fg_process_step_id is not null
						begin
							select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @fg_process_step_id
							select @temp_product_name = Part_Number from Product where ProcessStep_ID =@fg_process_step_id
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@fg_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
						end
	
						fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
					end

				close @mark_fg_track_cursor
				deallocate @mark_fg_track_cursor

				fetch next from @sort_track_cursor into @assembly_track_id, @assembly_process_step_id
			end
		close @sort_track_cursor
		deallocate @sort_track_cursor

	end
	else if @temp_ps_type_name = 'TEST' or @temp_ps_type_name = 'Class Store'
	begin
		--select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @process_step_id
		--insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@process_step_id, @temp_ps_type, @temp_ps_type_name)
		set @flag = 0
		select @temp_assembly_track_id = AssemblyTrack_ID from Test_Track_Table where ProcessStep_ID = @process_step_id

		declare test_track_cursor CURSOR for
		select AssemblyTrack_ID, ProcessStep_ID from Test_Track_Table where AssemblyTrack_ID = @temp_assembly_track_id order by Test_Track_Table.Sequence
		open test_track_cursor
			fetch next from test_track_cursor into @assembly_track_id, @temp_process_step_id
			while @@fetch_status = 0
			begin
				if @temp_process_step_id = @process_step_id
				begin
					set @flag = 1 
				end
				if @flag = 1
				begin
					if @temp_process_step_id is not null
					begin
						select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @temp_process_step_id
						insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@temp_process_step_id, @ps_type, @ps_type_name)
					end
				end
				fetch next from test_track_cursor into @assembly_track_id, @temp_process_step_id
			end
			close test_track_cursor
			deallocate test_track_cursor

			set @mark_fg_track_cursor = CURSOR FOR
			select Mark_Pack_ProcessStep_ID, FG_ProcessStep_ID from Mark_FG_Track_Table where AssemblyTrack_ID = @assembly_track_id

			open @mark_fg_track_cursor

			fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
			while @@fetch_status = 0
			begin
				if @mark_process_step_id is not null
				begin

					select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @mark_process_step_id
					select @temp_product_name = Part_Number from Product where ProcessStep_ID =@mark_process_step_id
					insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@mark_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)

				end
				if @fg_process_step_id is not null
				begin
					select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @fg_process_step_id
					select @temp_product_name = Part_Number from Product where ProcessStep_ID =@fg_process_step_id
					insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@fg_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
				end
				fetch next from @mark_fg_track_cursor into @mark_process_step_id, @fg_process_step_id
			end

			close @mark_fg_track_cursor
			deallocate @mark_fg_track_cursor

	end
	else if @temp_ps_type_name = 'Mark & Pack'
	begin
		select @mark_process_step_id = Mark_Pack_ProcessStep_ID, @fg_process_step_id=FG_ProcessStep_ID from Mark_FG_Track_Table where Mark_Pack_ProcessStep_ID = @process_step_id
		if @mark_process_step_id is not null
		begin

			select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @mark_process_step_id
			select @temp_product_name = Part_Number from Product where ProcessStep_ID =@mark_process_step_id
			insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@mark_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)

		end
		if @fg_process_step_id is not null
		begin
			select @ps_type=PS_Type, @ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @fg_process_step_id
			select @temp_product_name = Part_Number from Product where ProcessStep_ID =@fg_process_step_id
			insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@fg_process_step_id, @ps_type+'-'+@temp_product_name, @ps_type_name)
		end

	end
	else 
	begin
		select @temp_ps_type=PS_Type, @temp_ps_type_name=PS_Type_Name from Process_Step where ProcessStep_ID = @process_step_id
		insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name) values(@process_step_id, @temp_ps_type, @temp_ps_type_name)
	end
	insert @fn_result select *from @result
	return
end




